package beans;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.ConnectionDataSource;
import domainObjects.Infraction;

public class InfractionBean {
	public ArrayList<Infraction> getInfractions() throws SQLException {
		ConnectionDataSource cds = new ConnectionDataSource();
		Connection conn = cds.getSimpleConnection();

		java.sql.Statement stmt = null;
		ResultSet results = null;
		ArrayList<Infraction> infractions = new ArrayList<Infraction>();
		try {
			stmt = conn.createStatement();
			String query = "SELECT \"ID\", \"Description\", \"NiveauGravite\" FROM \"Infraction\";";
			results = stmt.executeQuery(query);
			while (results.next()) {
				infractions.add(new Infraction(results.getInt(1), results
						.getString(2), results.getInt(3)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null && !stmt.isClosed()) {
				stmt.close();
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
			if (results != null && !results.isClosed()) {
				results.close();
			}
		}
		return infractions;
	}
	
	public ArrayList<Infraction> getDossierInfractions(long dossierID) throws SQLException
	{
		ConnectionDataSource cds = new ConnectionDataSource();
		Connection conn = cds.getSimpleConnection();
		ResultSet results = null;
		PreparedStatement pstmt = null;
		ArrayList<Infraction> infractions = new ArrayList<Infraction>();
		try {		
			String query = "SELECT \"Infraction\".\"ID\",\"Infraction\".\"Description\",\"Infraction\".\"NiveauGravite\" FROM \"Infraction\", \"DossierIntractions\" WHERE \"Infraction\".\"ID\" = \"DossierIntractions\".\"InfractionID\" AND \"DossierIntractions\".\"DossierID\" = ?;";
			pstmt = conn.prepareStatement(query);
		   	pstmt.setLong(1, dossierID);
			results = pstmt.executeQuery();
			while (results.next()) {
				infractions.add(new Infraction(results.getInt(1), results.getString(2), results.getInt(3)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null && !pstmt.isClosed()) {
				pstmt.close();
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
			if (results != null && !results.isClosed()) {
				results.close();
			}
		}
		return infractions;
	}

	public void addInfraction(String description, int niveau)
			throws SQLException {
		if (description.trim() == "") {
			throw new IllegalArgumentException("Description");
		}
		if (niveau <= 0 || niveau > 10) {
			throw new IllegalArgumentException("niveau");
		}
		ConnectionDataSource cds = new ConnectionDataSource();
		Connection conn = cds.getSimpleConnection();
		PreparedStatement pstmt = null;
		try {
			String query = "INSERT INTO \"Infraction\"(\"Description\", \"NiveauGravite\") VALUES (?, ?);";
			pstmt = conn.prepareStatement(query); // create a statement
			pstmt.setString(1, description); // set input parameter 1
			pstmt.setInt(2, niveau); // set input parameter 2
			pstmt.executeUpdate(); // execute insert statement
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null && !pstmt.isClosed()) {
				pstmt.close();
			}
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		}

	}

}
